package com.edu.demo.utils;

import com.edu.demo.entity.Student;
import com.edu.demo.entity.Teacher;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @ClassName: ExcleUtils
 * @Description:
 * @Author: jxw
 * @Date: 2023-05-16
 */
@Slf4j //打印日志
public class ExcelUtils {

    public static Map<String,Integer> deptMap;

    public static List<Student> excelToStudents(InputStream inputStream,String fileName) {
        Workbook workbook = null;
        BCryptPasswordEncoder b = new BCryptPasswordEncoder();//加密密码
        List<Student> students = new ArrayList<>();
        try {
            // 根据传递过来的文件输入流创建一个workbook对象（对应Excel中的工作簿）
            workbook =getWorkbook(inputStream,fileName) ;
            // 创建完成，关闭输入流
            inputStream.close();
            //获取工作表对象，即第一个工作表 （工作簿里面有很多张工作表，这里取第一张工作表）
            Sheet sheet = workbook.getSheetAt(0);
            //获取工作表的总行数
            int rowLength = sheet.getLastRowNum() + 1;
//            System.out.println("总行数有多少行" + rowLength);
            //获取工作表第一行数据
            Row row = sheet.getRow(0);
            //获取工作表总列数的长度
            int colLength = row.getLastCellNum();
//            System.out.println("总列数有多少列" + colLength);
            // 创建一个单元格对象
            Cell cell = null;
            Student student = null;
            // 双重循环 因为一个单元格由行和列的索引下表构成
            for (int a = 1; a < rowLength; a++) {
                student = new Student();

                for (int i = 0; i < colLength; i++) {
                    // 分别取出第 a行 b列的单元格数据
                    cell = sheet.getRow(a).getCell(i);
                    // 设置单元格的类型是String类型
                    cell.setCellType(CellType.STRING);
                    // 获取单元格的数据
                    String strCellValue = cell.getStringCellValue();
                    switch (i) {
                        case 0:
                            student.setCode(strCellValue);
                            break;
                        case 1:
                            student.setName(strCellValue);
                            break;
                        case 2:
                            student.setPassword(b.encode(strCellValue));
                            break;
                        case 3:
                            student.setClazz(strCellValue);
                            break;
                        case 4:
                            student.setPhone(strCellValue);
                            break;
                        case 5:
                            student.setDeptId(deptMap.get(strCellValue));
                            student.setDeptName(strCellValue);
                            break;
                        case 6:
                            student.setGrade(strCellValue);
                            break;
                        case 7:
                            student.setMajor(strCellValue);
                            break;
                        case 8:
                            student.setSex(strCellValue);
                            break;


                    }

                }
                students.add(student);
            }
        }catch (Exception e){
                log.error("解析excel出错");
                log.error(e.getMessage());
        }


        return students;
    }


    public static List<Teacher> excelToTeachers(InputStream inputStream,String fileName) {
        Workbook workbook = null;
        BCryptPasswordEncoder b = new BCryptPasswordEncoder();//加密密码
        List<Teacher> teachers = new ArrayList<>();
        try {
            // 根据传递过来的文件输入流创建一个workbook对象（对应Excel中的工作簿）
            workbook = getWorkbook(inputStream,fileName) ;
            // 创建完成，关闭输入流
            inputStream.close();
            //获取工作表对象，即第一个工作表 （工作簿里面有很多张工作表，这里取第一张工作表）
            Sheet sheet = workbook.getSheetAt(0);
            //获取工作表的总行数
            int rowLength = sheet.getLastRowNum() + 1;
//            System.out.println("总行数有多少行" + rowLength);
            //获取工作表第一行数据
            Row row = sheet.getRow(0);
            //获取工作表总列数的长度
            int colLength = row.getLastCellNum();
//            System.out.println("总列数有多少列" + colLength);
            // 创建一个单元格对象
            Cell cell = null;
            Teacher teacher = null;
            // 双重循环 因为一个单元格由行和列的索引下表构成
            for (int a = 1; a < rowLength; a++) {
                teacher = new Teacher();
                teacher.setCreateBy(SecurityUtils.getSysUser().getName());
                for (int i = 0; i < colLength; i++) {
                    // 分别取出第 a行 b列的单元格数据
                    cell = sheet.getRow(a).getCell(i);
                    // 设置单元格的类型是String类型
                    cell.setCellType(CellType.STRING);
                    // 获取单元格的数据
                    String strCellValue = cell.getStringCellValue();
                    switch (i) {
                        case 0:
                            teacher.setCode(strCellValue);
                            break;
                        case 1:
                            teacher.setName(strCellValue);
                            break;
                        case 2:
                            teacher.setPhone(strCellValue);
                            break;
                        case 3:
                            teacher.setEmail(strCellValue);
                            break;
                        case 4:
                            teacher.setSex(strCellValue);
                            break;
                        case 5:
                            teacher.setPassword(b.encode(strCellValue));
                            break;
                        case 6:
                            teacher.setDeptName(strCellValue);
                            teacher.setDeptId(deptMap.get(strCellValue));
                            break;

                    }

                }
                teachers.add(teacher);
            }
        }catch (Exception e){
            log.error("解析excel出错");
            log.error(e.getMessage());
        }


        return teachers;
    }


    public static Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        return workbook;
    }
}
